{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import tushare as ts\n",
    "import os\n",
    "from datetime import datetime\n",
    "import time\n",
    "from tqdm.notebook import tqdm\n",
    "from utils import get_adj_data\n",
    "\n",
    "token = 'd942e6ff0e981f76aaa544f84405583e0c2129a8c82213637835a099'\n",
    "pro = ts.pro_api(token)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_data_picture(code):\n",
    "    dd = get_adj_data(code)\n",
    "    dd = dd[['trade_date','open','high','low','close','vol','amount']]\n",
    "    dd1 = pro.daily_basic(ts_code=code,fields='trade_date,turnover_rate,turnover_rate_f')\n",
    "    dd = pd.merge(dd,dd1,on=['trade_date'],how='outer')\n",
    "    dd['vwap'] = 1000*dd['amount']/(100*dd['vol'])\n",
    "    dd['return1'] = np.log(dd['close']).diff().fillna(0)\n",
    "    dd.index = pd.to_datetime(dd['trade_date'])\n",
    "    dd = dd.rename(columns={'vol':'volume','turnover_rate':'turn','turnover_rate_f':'free_turn'})\n",
    "    dd = dd.drop(['trade_date','amount'],axis=1)\n",
    "    dd = dd[['open','high','low','close','vwap','volume','return1','turn','free_turn']]\n",
    "    return dd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "5e5fff05d5ab47d09920aa449341bd49",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "HBox(children=(HTML(value=''), FloatProgress(value=0.0, max=4457.0), HTML(value='')))"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n"
     ]
    }
   ],
   "source": [
    "# 存储所有利润表数据，如果第一次运行，则需等待十多分钟\n",
    "folder = '基础数据'\n",
    "if not os.path.exists(folder):\n",
    "    os.mkdir(folder)\n",
    "df_stocks = pro.stock_basic()\n",
    "for i in tqdm(list(reversed(range(0,len(df_stocks)-1)))):\n",
    "    code = df_stocks.loc[i,'ts_code']\n",
    "    if not os.path.exists(f'{folder}/{code}.csv'):\n",
    "        dd = get_data_picture(code)\n",
    "        dd.to_csv(f'{folder}/{code}.csv')\n",
    "        time.sleep(0.01)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "folder = '基础数据/'\n",
    "files = os.listdir(folder)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "dd_calender = pro.trade_cal(exchange='SSE')\n",
    "dd_calender = dd_calender[dd_calender['is_open']==1]\n",
    "dd_calender = dd_calender.sort_values(by=['cal_date'])\n",
    "dd_calender.index = pd.to_datetime(dd_calender['cal_date'])\n",
    "file = files[13]\n",
    "df = pd.read_csv(folder+file,index_col=0).sort_index()\n",
    "df.index = pd.to_datetime(df.index)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>open</th>\n",
       "      <th>high</th>\n",
       "      <th>low</th>\n",
       "      <th>close</th>\n",
       "      <th>vwap</th>\n",
       "      <th>volume</th>\n",
       "      <th>return1</th>\n",
       "      <th>turn</th>\n",
       "      <th>free_turn</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>trade_date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1998-12-23</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.2731</td>\n",
       "      <td>3.5531</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1998-12-24</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.6279</td>\n",
       "      <td>1.7524</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1998-12-25</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.4029</td>\n",
       "      <td>1.1244</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1998-12-26</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1998-12-27</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-08-27</th>\n",
       "      <td>3.95</td>\n",
       "      <td>4.12</td>\n",
       "      <td>3.77</td>\n",
       "      <td>3.94</td>\n",
       "      <td>3.905799</td>\n",
       "      <td>137561.00</td>\n",
       "      <td>-0.005063</td>\n",
       "      <td>4.5403</td>\n",
       "      <td>5.7443</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-08-30</th>\n",
       "      <td>3.88</td>\n",
       "      <td>3.91</td>\n",
       "      <td>3.78</td>\n",
       "      <td>3.80</td>\n",
       "      <td>3.844328</td>\n",
       "      <td>52494.60</td>\n",
       "      <td>-0.036180</td>\n",
       "      <td>1.7326</td>\n",
       "      <td>2.1921</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-08-31</th>\n",
       "      <td>3.78</td>\n",
       "      <td>3.83</td>\n",
       "      <td>3.70</td>\n",
       "      <td>3.75</td>\n",
       "      <td>3.762242</td>\n",
       "      <td>41403.00</td>\n",
       "      <td>-0.013245</td>\n",
       "      <td>1.3665</td>\n",
       "      <td>1.7289</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-09-01</th>\n",
       "      <td>3.75</td>\n",
       "      <td>3.79</td>\n",
       "      <td>3.70</td>\n",
       "      <td>3.71</td>\n",
       "      <td>3.737675</td>\n",
       "      <td>35044.68</td>\n",
       "      <td>-0.010724</td>\n",
       "      <td>1.1567</td>\n",
       "      <td>1.4634</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-09-02</th>\n",
       "      <td>3.70</td>\n",
       "      <td>3.78</td>\n",
       "      <td>3.66</td>\n",
       "      <td>3.78</td>\n",
       "      <td>3.737259</td>\n",
       "      <td>35210.97</td>\n",
       "      <td>0.018692</td>\n",
       "      <td>1.1622</td>\n",
       "      <td>1.4704</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>4500 rows × 9 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            open  high   low  close      vwap     volume   return1    turn  \\\n",
       "trade_date                                                                   \n",
       "1998-12-23   NaN   NaN   NaN    NaN       NaN        NaN  0.000000  1.2731   \n",
       "1998-12-24   NaN   NaN   NaN    NaN       NaN        NaN  0.000000  0.6279   \n",
       "1998-12-25   NaN   NaN   NaN    NaN       NaN        NaN  0.000000  0.4029   \n",
       "1998-12-26   NaN   NaN   NaN    NaN       NaN        NaN  0.000000     NaN   \n",
       "1998-12-27   NaN   NaN   NaN    NaN       NaN        NaN  0.000000     NaN   \n",
       "...          ...   ...   ...    ...       ...        ...       ...     ...   \n",
       "2021-08-27  3.95  4.12  3.77   3.94  3.905799  137561.00 -0.005063  4.5403   \n",
       "2021-08-30  3.88  3.91  3.78   3.80  3.844328   52494.60 -0.036180  1.7326   \n",
       "2021-08-31  3.78  3.83  3.70   3.75  3.762242   41403.00 -0.013245  1.3665   \n",
       "2021-09-01  3.75  3.79  3.70   3.71  3.737675   35044.68 -0.010724  1.1567   \n",
       "2021-09-02  3.70  3.78  3.66   3.78  3.737259   35210.97  0.018692  1.1622   \n",
       "\n",
       "            free_turn  \n",
       "trade_date             \n",
       "1998-12-23     3.5531  \n",
       "1998-12-24     1.7524  \n",
       "1998-12-25     1.1244  \n",
       "1998-12-26        NaN  \n",
       "1998-12-27        NaN  \n",
       "...               ...  \n",
       "2021-08-27     5.7443  \n",
       "2021-08-30     2.1921  \n",
       "2021-08-31     1.7289  \n",
       "2021-09-01     1.4634  \n",
       "2021-09-02     1.4704  \n",
       "\n",
       "[4500 rows x 9 columns]"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>exchange</th>\n",
       "      <th>cal_date</th>\n",
       "      <th>is_open</th>\n",
       "      <th>open</th>\n",
       "      <th>high</th>\n",
       "      <th>low</th>\n",
       "      <th>close</th>\n",
       "      <th>vwap</th>\n",
       "      <th>volume</th>\n",
       "      <th>return1</th>\n",
       "      <th>turn</th>\n",
       "      <th>free_turn</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1990-12-19</th>\n",
       "      <td>SSE</td>\n",
       "      <td>19901219</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1990-12-20</th>\n",
       "      <td>SSE</td>\n",
       "      <td>19901220</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1990-12-21</th>\n",
       "      <td>SSE</td>\n",
       "      <td>19901221</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1990-12-24</th>\n",
       "      <td>SSE</td>\n",
       "      <td>19901224</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1990-12-25</th>\n",
       "      <td>SSE</td>\n",
       "      <td>19901225</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-12-27</th>\n",
       "      <td>SSE</td>\n",
       "      <td>20211227</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-12-28</th>\n",
       "      <td>SSE</td>\n",
       "      <td>20211228</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-12-29</th>\n",
       "      <td>SSE</td>\n",
       "      <td>20211229</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-12-30</th>\n",
       "      <td>SSE</td>\n",
       "      <td>20211230</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-12-31</th>\n",
       "      <td>SSE</td>\n",
       "      <td>20211231</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>7714 rows × 12 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           exchange  cal_date  is_open  open  high  low  close  vwap  volume  \\\n",
       "1990-12-19      SSE  19901219      1.0   NaN   NaN  NaN    NaN   NaN     NaN   \n",
       "1990-12-20      SSE  19901220      1.0   NaN   NaN  NaN    NaN   NaN     NaN   \n",
       "1990-12-21      SSE  19901221      1.0   NaN   NaN  NaN    NaN   NaN     NaN   \n",
       "1990-12-24      SSE  19901224      1.0   NaN   NaN  NaN    NaN   NaN     NaN   \n",
       "1990-12-25      SSE  19901225      1.0   NaN   NaN  NaN    NaN   NaN     NaN   \n",
       "...             ...       ...      ...   ...   ...  ...    ...   ...     ...   \n",
       "2021-12-27      SSE  20211227      1.0   NaN   NaN  NaN    NaN   NaN     NaN   \n",
       "2021-12-28      SSE  20211228      1.0   NaN   NaN  NaN    NaN   NaN     NaN   \n",
       "2021-12-29      SSE  20211229      1.0   NaN   NaN  NaN    NaN   NaN     NaN   \n",
       "2021-12-30      SSE  20211230      1.0   NaN   NaN  NaN    NaN   NaN     NaN   \n",
       "2021-12-31      SSE  20211231      1.0   NaN   NaN  NaN    NaN   NaN     NaN   \n",
       "\n",
       "            return1  turn  free_turn  \n",
       "1990-12-19      NaN   NaN        NaN  \n",
       "1990-12-20      NaN   NaN        NaN  \n",
       "1990-12-21      NaN   NaN        NaN  \n",
       "1990-12-24      NaN   NaN        NaN  \n",
       "1990-12-25      NaN   NaN        NaN  \n",
       "...             ...   ...        ...  \n",
       "2021-12-27      NaN   NaN        NaN  \n",
       "2021-12-28      NaN   NaN        NaN  \n",
       "2021-12-29      NaN   NaN        NaN  \n",
       "2021-12-30      NaN   NaN        NaN  \n",
       "2021-12-31      NaN   NaN        NaN  \n",
       "\n",
       "[7714 rows x 12 columns]"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.concat([dd_calender,df],axis=1).sort_index()\n",
    "df = df[df.index.isin(df.shift(-1).dropna().index)].dropna()\n",
    "df = \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>exchange</th>\n",
       "      <th>cal_date</th>\n",
       "      <th>is_open</th>\n",
       "      <th>open</th>\n",
       "      <th>high</th>\n",
       "      <th>low</th>\n",
       "      <th>close</th>\n",
       "      <th>vwap</th>\n",
       "      <th>volume</th>\n",
       "      <th>return1</th>\n",
       "      <th>turn</th>\n",
       "      <th>free_turn</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2001-02-06</th>\n",
       "      <td>SSE</td>\n",
       "      <td>20010206</td>\n",
       "      <td>1.0</td>\n",
       "      <td>7.288558</td>\n",
       "      <td>7.341703</td>\n",
       "      <td>7.038013</td>\n",
       "      <td>7.167082</td>\n",
       "      <td>9.379211</td>\n",
       "      <td>3880.71</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.5056</td>\n",
       "      <td>1.4112</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2001-02-07</th>\n",
       "      <td>SSE</td>\n",
       "      <td>20010207</td>\n",
       "      <td>1.0</td>\n",
       "      <td>7.250596</td>\n",
       "      <td>7.273373</td>\n",
       "      <td>7.136713</td>\n",
       "      <td>7.151897</td>\n",
       "      <td>9.495809</td>\n",
       "      <td>3887.23</td>\n",
       "      <td>-0.002121</td>\n",
       "      <td>0.5065</td>\n",
       "      <td>1.4135</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2001-02-08</th>\n",
       "      <td>SSE</td>\n",
       "      <td>20010208</td>\n",
       "      <td>1.0</td>\n",
       "      <td>7.060790</td>\n",
       "      <td>7.113936</td>\n",
       "      <td>6.802654</td>\n",
       "      <td>6.833023</td>\n",
       "      <td>9.069054</td>\n",
       "      <td>5514.20</td>\n",
       "      <td>-0.045611</td>\n",
       "      <td>0.7184</td>\n",
       "      <td>2.0052</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2001-02-09</th>\n",
       "      <td>SSE</td>\n",
       "      <td>20010209</td>\n",
       "      <td>1.0</td>\n",
       "      <td>6.833023</td>\n",
       "      <td>7.015237</td>\n",
       "      <td>6.833023</td>\n",
       "      <td>6.924130</td>\n",
       "      <td>9.109257</td>\n",
       "      <td>3721.00</td>\n",
       "      <td>0.013245</td>\n",
       "      <td>0.4848</td>\n",
       "      <td>1.3531</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2001-02-12</th>\n",
       "      <td>SSE</td>\n",
       "      <td>20010212</td>\n",
       "      <td>1.0</td>\n",
       "      <td>6.612848</td>\n",
       "      <td>6.802654</td>\n",
       "      <td>6.574886</td>\n",
       "      <td>6.574886</td>\n",
       "      <td>8.690087</td>\n",
       "      <td>18567.13</td>\n",
       "      <td>-0.051755</td>\n",
       "      <td>2.4191</td>\n",
       "      <td>6.7517</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-08-27</th>\n",
       "      <td>SSE</td>\n",
       "      <td>20210827</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3.950000</td>\n",
       "      <td>4.120000</td>\n",
       "      <td>3.770000</td>\n",
       "      <td>3.940000</td>\n",
       "      <td>3.905799</td>\n",
       "      <td>137561.00</td>\n",
       "      <td>-0.005063</td>\n",
       "      <td>4.5403</td>\n",
       "      <td>5.7443</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-08-30</th>\n",
       "      <td>SSE</td>\n",
       "      <td>20210830</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3.880000</td>\n",
       "      <td>3.910000</td>\n",
       "      <td>3.780000</td>\n",
       "      <td>3.800000</td>\n",
       "      <td>3.844328</td>\n",
       "      <td>52494.60</td>\n",
       "      <td>-0.036180</td>\n",
       "      <td>1.7326</td>\n",
       "      <td>2.1921</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-08-31</th>\n",
       "      <td>SSE</td>\n",
       "      <td>20210831</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3.780000</td>\n",
       "      <td>3.830000</td>\n",
       "      <td>3.700000</td>\n",
       "      <td>3.750000</td>\n",
       "      <td>3.762242</td>\n",
       "      <td>41403.00</td>\n",
       "      <td>-0.013245</td>\n",
       "      <td>1.3665</td>\n",
       "      <td>1.7289</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-09-01</th>\n",
       "      <td>SSE</td>\n",
       "      <td>20210901</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3.750000</td>\n",
       "      <td>3.790000</td>\n",
       "      <td>3.700000</td>\n",
       "      <td>3.710000</td>\n",
       "      <td>3.737675</td>\n",
       "      <td>35044.68</td>\n",
       "      <td>-0.010724</td>\n",
       "      <td>1.1567</td>\n",
       "      <td>1.4634</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-09-02</th>\n",
       "      <td>SSE</td>\n",
       "      <td>20210902</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3.700000</td>\n",
       "      <td>3.780000</td>\n",
       "      <td>3.660000</td>\n",
       "      <td>3.780000</td>\n",
       "      <td>3.737259</td>\n",
       "      <td>35210.97</td>\n",
       "      <td>0.018692</td>\n",
       "      <td>1.1622</td>\n",
       "      <td>1.4704</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3877 rows × 12 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           exchange  cal_date  is_open      open      high       low  \\\n",
       "2001-02-06      SSE  20010206      1.0  7.288558  7.341703  7.038013   \n",
       "2001-02-07      SSE  20010207      1.0  7.250596  7.273373  7.136713   \n",
       "2001-02-08      SSE  20010208      1.0  7.060790  7.113936  6.802654   \n",
       "2001-02-09      SSE  20010209      1.0  6.833023  7.015237  6.833023   \n",
       "2001-02-12      SSE  20010212      1.0  6.612848  6.802654  6.574886   \n",
       "...             ...       ...      ...       ...       ...       ...   \n",
       "2021-08-27      SSE  20210827      1.0  3.950000  4.120000  3.770000   \n",
       "2021-08-30      SSE  20210830      1.0  3.880000  3.910000  3.780000   \n",
       "2021-08-31      SSE  20210831      1.0  3.780000  3.830000  3.700000   \n",
       "2021-09-01      SSE  20210901      1.0  3.750000  3.790000  3.700000   \n",
       "2021-09-02      SSE  20210902      1.0  3.700000  3.780000  3.660000   \n",
       "\n",
       "               close      vwap     volume   return1    turn  free_turn  \n",
       "2001-02-06  7.167082  9.379211    3880.71  0.000000  0.5056     1.4112  \n",
       "2001-02-07  7.151897  9.495809    3887.23 -0.002121  0.5065     1.4135  \n",
       "2001-02-08  6.833023  9.069054    5514.20 -0.045611  0.7184     2.0052  \n",
       "2001-02-09  6.924130  9.109257    3721.00  0.013245  0.4848     1.3531  \n",
       "2001-02-12  6.574886  8.690087   18567.13 -0.051755  2.4191     6.7517  \n",
       "...              ...       ...        ...       ...     ...        ...  \n",
       "2021-08-27  3.940000  3.905799  137561.00 -0.005063  4.5403     5.7443  \n",
       "2021-08-30  3.800000  3.844328   52494.60 -0.036180  1.7326     2.1921  \n",
       "2021-08-31  3.750000  3.762242   41403.00 -0.013245  1.3665     1.7289  \n",
       "2021-09-01  3.710000  3.737675   35044.68 -0.010724  1.1567     1.4634  \n",
       "2021-09-02  3.780000  3.737259   35210.97  0.018692  1.1622     1.4704  \n",
       "\n",
       "[3877 rows x 12 columns]"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.dropna()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>exchange</th>\n",
       "      <th>cal_date</th>\n",
       "      <th>is_open</th>\n",
       "      <th>open</th>\n",
       "      <th>high</th>\n",
       "      <th>low</th>\n",
       "      <th>close</th>\n",
       "      <th>vwap</th>\n",
       "      <th>volume</th>\n",
       "      <th>return1</th>\n",
       "      <th>turn</th>\n",
       "      <th>free_turn</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2001-02-06</th>\n",
       "      <td>SSE</td>\n",
       "      <td>20010206</td>\n",
       "      <td>1.0</td>\n",
       "      <td>7.288558</td>\n",
       "      <td>7.341703</td>\n",
       "      <td>7.038013</td>\n",
       "      <td>7.167082</td>\n",
       "      <td>9.379211</td>\n",
       "      <td>3880.71</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.5056</td>\n",
       "      <td>1.4112</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2001-02-07</th>\n",
       "      <td>SSE</td>\n",
       "      <td>20010207</td>\n",
       "      <td>1.0</td>\n",
       "      <td>7.250596</td>\n",
       "      <td>7.273373</td>\n",
       "      <td>7.136713</td>\n",
       "      <td>7.151897</td>\n",
       "      <td>9.495809</td>\n",
       "      <td>3887.23</td>\n",
       "      <td>-0.002121</td>\n",
       "      <td>0.5065</td>\n",
       "      <td>1.4135</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2001-02-08</th>\n",
       "      <td>SSE</td>\n",
       "      <td>20010208</td>\n",
       "      <td>1.0</td>\n",
       "      <td>7.060790</td>\n",
       "      <td>7.113936</td>\n",
       "      <td>6.802654</td>\n",
       "      <td>6.833023</td>\n",
       "      <td>9.069054</td>\n",
       "      <td>5514.20</td>\n",
       "      <td>-0.045611</td>\n",
       "      <td>0.7184</td>\n",
       "      <td>2.0052</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2001-02-09</th>\n",
       "      <td>SSE</td>\n",
       "      <td>20010209</td>\n",
       "      <td>1.0</td>\n",
       "      <td>6.833023</td>\n",
       "      <td>7.015237</td>\n",
       "      <td>6.833023</td>\n",
       "      <td>6.924130</td>\n",
       "      <td>9.109257</td>\n",
       "      <td>3721.00</td>\n",
       "      <td>0.013245</td>\n",
       "      <td>0.4848</td>\n",
       "      <td>1.3531</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2001-02-12</th>\n",
       "      <td>SSE</td>\n",
       "      <td>20010212</td>\n",
       "      <td>1.0</td>\n",
       "      <td>6.612848</td>\n",
       "      <td>6.802654</td>\n",
       "      <td>6.574886</td>\n",
       "      <td>6.574886</td>\n",
       "      <td>8.690087</td>\n",
       "      <td>18567.13</td>\n",
       "      <td>-0.051755</td>\n",
       "      <td>2.4191</td>\n",
       "      <td>6.7517</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-08-26</th>\n",
       "      <td>SSE</td>\n",
       "      <td>20210826</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3.810000</td>\n",
       "      <td>3.960000</td>\n",
       "      <td>3.770000</td>\n",
       "      <td>3.960000</td>\n",
       "      <td>3.870350</td>\n",
       "      <td>79281.00</td>\n",
       "      <td>0.038615</td>\n",
       "      <td>2.6167</td>\n",
       "      <td>3.3107</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-08-27</th>\n",
       "      <td>SSE</td>\n",
       "      <td>20210827</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3.950000</td>\n",
       "      <td>4.120000</td>\n",
       "      <td>3.770000</td>\n",
       "      <td>3.940000</td>\n",
       "      <td>3.905799</td>\n",
       "      <td>137561.00</td>\n",
       "      <td>-0.005063</td>\n",
       "      <td>4.5403</td>\n",
       "      <td>5.7443</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-08-30</th>\n",
       "      <td>SSE</td>\n",
       "      <td>20210830</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3.880000</td>\n",
       "      <td>3.910000</td>\n",
       "      <td>3.780000</td>\n",
       "      <td>3.800000</td>\n",
       "      <td>3.844328</td>\n",
       "      <td>52494.60</td>\n",
       "      <td>-0.036180</td>\n",
       "      <td>1.7326</td>\n",
       "      <td>2.1921</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-08-31</th>\n",
       "      <td>SSE</td>\n",
       "      <td>20210831</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3.780000</td>\n",
       "      <td>3.830000</td>\n",
       "      <td>3.700000</td>\n",
       "      <td>3.750000</td>\n",
       "      <td>3.762242</td>\n",
       "      <td>41403.00</td>\n",
       "      <td>-0.013245</td>\n",
       "      <td>1.3665</td>\n",
       "      <td>1.7289</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-09-01</th>\n",
       "      <td>SSE</td>\n",
       "      <td>20210901</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3.750000</td>\n",
       "      <td>3.790000</td>\n",
       "      <td>3.700000</td>\n",
       "      <td>3.710000</td>\n",
       "      <td>3.737675</td>\n",
       "      <td>35044.68</td>\n",
       "      <td>-0.010724</td>\n",
       "      <td>1.1567</td>\n",
       "      <td>1.4634</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3838 rows × 12 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           exchange  cal_date  is_open      open      high       low  \\\n",
       "2001-02-06      SSE  20010206      1.0  7.288558  7.341703  7.038013   \n",
       "2001-02-07      SSE  20010207      1.0  7.250596  7.273373  7.136713   \n",
       "2001-02-08      SSE  20010208      1.0  7.060790  7.113936  6.802654   \n",
       "2001-02-09      SSE  20010209      1.0  6.833023  7.015237  6.833023   \n",
       "2001-02-12      SSE  20010212      1.0  6.612848  6.802654  6.574886   \n",
       "...             ...       ...      ...       ...       ...       ...   \n",
       "2021-08-26      SSE  20210826      1.0  3.810000  3.960000  3.770000   \n",
       "2021-08-27      SSE  20210827      1.0  3.950000  4.120000  3.770000   \n",
       "2021-08-30      SSE  20210830      1.0  3.880000  3.910000  3.780000   \n",
       "2021-08-31      SSE  20210831      1.0  3.780000  3.830000  3.700000   \n",
       "2021-09-01      SSE  20210901      1.0  3.750000  3.790000  3.700000   \n",
       "\n",
       "               close      vwap     volume   return1    turn  free_turn  \n",
       "2001-02-06  7.167082  9.379211    3880.71  0.000000  0.5056     1.4112  \n",
       "2001-02-07  7.151897  9.495809    3887.23 -0.002121  0.5065     1.4135  \n",
       "2001-02-08  6.833023  9.069054    5514.20 -0.045611  0.7184     2.0052  \n",
       "2001-02-09  6.924130  9.109257    3721.00  0.013245  0.4848     1.3531  \n",
       "2001-02-12  6.574886  8.690087   18567.13 -0.051755  2.4191     6.7517  \n",
       "...              ...       ...        ...       ...     ...        ...  \n",
       "2021-08-26  3.960000  3.870350   79281.00  0.038615  2.6167     3.3107  \n",
       "2021-08-27  3.940000  3.905799  137561.00 -0.005063  4.5403     5.7443  \n",
       "2021-08-30  3.800000  3.844328   52494.60 -0.036180  1.7326     2.1921  \n",
       "2021-08-31  3.750000  3.762242   41403.00 -0.013245  1.3665     1.7289  \n",
       "2021-09-01  3.710000  3.737675   35044.68 -0.010724  1.1567     1.4634  \n",
       "\n",
       "[3838 rows x 12 columns]"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[df.index.isin(df.shift(-1).dropna().index)].dropna()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
